﻿using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Drawing;
using System.IO;
using System.Xml;

using System.Data;
using System.Data.OleDb;

using Microsoft.AnalysisServices.AdomdClient;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;

namespace KoPages.CONTROLTEMPLATES.Ko
{
    /// <summary>
    /// http://www.codeproject.com/KB/database/dotnet_adomd_mdx_grid.aspx
    /// <KoOLAP:Cmd runat="server" id="idTrade" />
    /// </summary>
    public partial class OLAP_Query : UserControl
    {
        #region Page Method
        protected void Page_Load(object sender, EventArgs e)
        {
            SPUtility.EnsureSiteAdminAccess(Microsoft.SharePoint.SPContext.Current.Web);

            if (!IsPostBack)
            {
                //setting default connection string value
                // @"Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=APP20;Initial Catalog=RetailAnalytics";
                txtConnStr.Text =
                    GetAppSetting(@"BIConnect");
                txtMDX.Text =
@"
SELECT NON EMPTY HIERARCHIZE( { [Дата].[Г-М-Д].[Месяц].&[201109] } ) ON COLUMNS,
{ [Measures].[Текущая выручка с квм], [Measures].[Средняя выручка с квм в дни продаж] } ON ROWS
FROM [KPI]
";
            }

            //clearing any error message
            lblErr.Text = "";
        }
        #endregion Page Method

        #region Common
        #region App Settings
        static public string GetAppSetting(String mName)
        {
            String vStr = null;
            SPWebService mSer = SPWebService.ContentService;
            #region Ver 1
            if (mSer != null)
            {
                foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
                {
                    if (mMod.Owner == mName)
                        vStr = mMod.Value;
                }
            }
            if (vStr != null)
            {
                vStr =
                    vStr.Substring(
                        vStr.IndexOf("value=") + 7
                        , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
            }
            #endregion Ver 1
            else
            #region Ver 2
            {
                string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(strPath);
                XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
                foreach (XmlNode keynode in node.ChildNodes)
                {
                    string keyStr = keynode.Attributes["key"].Value;
                    if (keyStr == mName)
                    {
                        vStr = keynode.Attributes["value"].Value;
                        break;
                    }
                }
            }
            #endregion Ver 2
            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        #endregion App Settings
        #endregion

        #region BI
        protected void btnGo_Click(object sender, EventArgs e)
        {
            //using try....catch block. If any error occurs we will display it using lblErr
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite(SPContext.Current.Web.Site.ID))
                    {
                        CellSet cst = GetCellset();
                        BuildGrid(cst);
                    }
                });

            }
            catch (System.Exception ex)
            {
                lblErr.Text = ex.Message;
            }

        }
        private CellSet GetCellset()
        {
            //Lets store the connection string and MDX query to local variables
            string strConn = txtConnStr.Text;
            string strMDX = txtMDX.Text;

            //create and open adomd connection with connection string
            AdomdConnection conn = new AdomdConnection(strConn);
            conn.Open();

            //create adomd command using connection and MDX query
            AdomdCommand cmd = new AdomdCommand(strMDX, conn);

            //The ExecuteCellSet method of adomd command will 
            //execute the MDX query and return CellSet object
            CellSet cst = cmd.ExecuteCellSet();

            //close connection
            conn.Close();

            //return cellset
            return cst;
        }
        private void BuildGrid(CellSet cst)
        {
            //check if any axes were returned else throw error.
            int axes_count = cst.Axes.Count;
            if (axes_count == 0)
                throw new Exception("No data returned for the selection");

            //if axes count is not 2
            if (axes_count != 2)
                throw new Exception("The sample code support only queries with two axes");

            //if no position on either row or column throw error
            if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
                throw new Exception("No data returned for the selection");

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;


            //Number of dimensions on the column
            col_dim_count = cst.Axes[0].Positions[0].Members.Count;

            //Number of dimensions on the row
            if (cst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = cst.Axes[1].Positions[0].Members.Count;

            //Total rows and columns
            row_count = cst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = cst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            //lets clear any controls under the grid panel
            gridPanel.Controls.Clear();

            //Add new server side table control to gridPanel
            Table tblGrid = new Table();
            tblGrid.CellSpacing = 0;
            tblGrid.Width = col_count * 100;
            gridPanel.Controls.Add(tblGrid);

            //We will use label control to add text to the table cell
            Label lbl;

            for (cur_row = 0; cur_row < row_count; cur_row++)
            {
                //add new row to table
                TableRow tr = new TableRow();
                tblGrid.Rows.Add(tr);

                for (cur_col = 0; cur_col < col_count; cur_col++)
                {
                    //create new cell and instance of label
                    TableCell td = new TableCell();
                    lbl = new Label();

                    //check if we are writing to a ROW having column header
                    if (cur_row < col_dim_count)
                    {
                        //check if we are writing to a cell having row header
                        if (cur_col < row_dim_count)
                        {
                            //this should be empty cell -- it's on top left of the grid.
                            lbl.Text = "&nbsp;";
                            td.CssClass = "titleAllLockedCell"; //this locks the cell so it doesn't scroll upwards nor leftwards
                        }
                        else
                        {
                            //this is a column header cell -- use member caption for header
                            lbl.Text = cst.Axes[0].Positions[cur_col - row_dim_count].Members[cur_row].Caption;
                            td.CssClass = "titleTopLockedCell"; // this lockeders the cell so it doesn't scroll upwards
                        }
                    }
                    else
                    {
                        //We are here.. so we are writing a row having data (not column headers)

                        //check if we are writing to a cell having row header
                        if (cur_col < row_dim_count)
                        {
                            //this is a row header cell -- use member caption for header
                            lbl.Text = cst.Axes[1].Positions[cur_row - col_dim_count].Members[cur_col].Caption;
                            td.CssClass = "titleLeftLockedCell"; // this lockeders the cell so it doesn't scroll leftwards
                        }
                        else
                        {
                            //this is data cell.. so we write the Formatted value of the cell.
                            lbl.Text = cst[cur_col - row_dim_count, cur_row - col_dim_count].FormattedValue + "&nbsp;";
                            td.CssClass = "valueCell"; //this  right aligns the values in the column
                        }

                        //turn the wrapping off for row header and data cells.
                        td.Wrap = false;
                    }

                    //add cell to the row.
                    td.Controls.Add(lbl);
                    tr.Cells.Add(td);
                }
            }

        }
        #endregion

    }
}
